package com.bit.cfdi.web.dao.impl;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.bit.cfdi.web.dao.ComprobanteDAO;
import com.bit.cfdi.web.domain.Comprobante;
import com.bit.cfdi.web.domain.Negocio;

@Repository
public class ComprobanteDAOImpl extends HibernateDaoSupport implements ComprobanteDAO {
	protected Logger log = LogManager.getLogger(this.getClass().getName());

	@Autowired
	public void init(final SessionFactory sessionFactory) {
		setSessionFactory(sessionFactory);
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional public List<Comprobante> getTotalPorClienteFecha(Long idCliente, 
			Calendar fechaInicio, Calendar fechaFin) throws DataAccessException {
		List<Comprobante> comprobantelist = null;
		try {
			comprobantelist = getHibernateTemplate().find(
					" from Comprobante as c inner join fetch c.cliente where c.cliente.idCliente=?"
							+ " and ((c.numeroParcialidad=0 and c.parcialidades=null) or c.numeroParcialidad=c.parcialidades)"
							+ " and (c.fechaCreacion>=? and c.fechaCreacion<=?) and c.tipoComprobante = 'ingreso'", idCliente, fechaInicio, fechaFin);
							//+ " and (c.fechaTimbrado>=? and c.fechaTimbrado<=?) and c.tipoComprobante = 'ingreso'", idCliente, fechaInicio, fechaFin);
		} catch (Exception e) {
			log.error("Error en obtener el total de comprobantes: " + e.getMessage());
		}
		return comprobantelist.size() > 0 ? comprobantelist : null;
	}

	@SuppressWarnings("rawtypes")
	@Override
	@Transactional public Double getSumTotalPorClienteFecha(Long idCliente, 
			Calendar fechaInicio, Calendar fechaFin) throws DataAccessException {
		Double total = new Double(0);
		try {
			List obj = getHibernateTemplate().find(
					"select sum(c.total * c.tipoDeCambio) from Comprobante as c where c.cliente.idCliente=?"
							+ " and ((c.numeroParcialidad=0 and c.parcialidades=null) or c.numeroParcialidad=c.parcialidades)"
							+ " and (c.fechaCreacion>=? and c.fechaCreacion<=?) and c.tipoComprobante = 'ingreso'", idCliente, fechaInicio, fechaFin);
							//+ " and (c.fechaTimbrado>=? and c.fechaTimbrado<=?) and c.tipoComprobante = 'ingreso'", idCliente, fechaInicio, fechaFin);
			if (null != obj.get(0)) {
				total = (Double) obj.get(0);
			}
		} catch (Exception e) {
			log.error("Error en obtener el total de comprobantes: " + e.getMessage());
		}

		return total;
	}

	@Override
	@Transactional public void save(Comprobante comprobante)  throws DataAccessException {
		getHibernateTemplate().saveOrUpdate(comprobante);
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional public Comprobante getById(Long id)  throws DataAccessException {
		List<Comprobante> comprobantes = getHibernateTemplate().find(
				" from Comprobante as comprobante" + " where comprobante.idComprobante=? ", id);
		return comprobantes.size() > 0 ? comprobantes.get(0) : null;
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional public Comprobante getByUUID(String UUID) throws DataAccessException {
		List<Comprobante> comprobantes = getHibernateTemplate().find(
				" from Comprobante as comprobante where comprobante.uuid=? ", UUID);
		return comprobantes.size() > 0 ? comprobantes.get(0) : null;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	@Transactional public Comprobante getBySerieFolioAndRFCEmisor(final String serie, 
			final String folio, final String rfcEmisor) throws DataAccessException {
		List<Comprobante> comprobantes = getHibernateTemplate().find(
				" from Comprobante as c" +
				" where c.negocio.datosFiscales.rfc = ?" +
				" and c.serie = ?" +
				" and c.folio = ?", 
				rfcEmisor, serie, folio);
		return comprobantes.size() > 0 ? comprobantes.get(0) : null;
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional public List<Comprobante> getAll() throws DataAccessException {
		List<Comprobante> comprobantes = 
				getHibernateTemplate().find(" from Comprobante as comprobante");
		return comprobantes;
	}

	@SuppressWarnings("rawtypes")
	@Override
	@Transactional public List getComprobanteByNegocio(Negocio negocio, 
			HashMap<String, Object> criterios, List<String> extras, int start,
			int end) throws DataAccessException {
		
		String startQuery = "select * from ( select newTable.*, ROW_NUMBER() OVER () AS rownumber from (";
		String endQuery = "order by fecha, serie, folio, receptor ) as newTable ) as final";													

		String queryPart1 = "select UCASE(comp.serie) as serie, cast(comp.folio as Integer) as folio, comp.fechaCreacion as " +
				"fecha, df.rfc as receptor, comp.uuid as uuid, comp.tipocomprobante as tipocfdi," // 6
				+ " case when comp.estatus=0 then varchar('Cancelado') when comp.estatus=1 then varchar('Vigente') when comp.estatus=2 then " +
				"varchar('Borrador') when comp.estatus=4 then varchar('Sin timbrar') end as estado, comp.moneda as moneda," // 2
				+ " comp.tipoDeCambio as tipoDeCambio, comp.subTotal as subTotal, comp.total as total, comp.idComprobante, mp.descripcion, " +
				"comp.montoparcialidades, comp.parcialidades, comp.numeroparcialidad, "//8
				+ " cc.liquidado, cc.fechaplaneadapago, comp.descuentoGeneral, comp.fechaTimbrado";

		String queryPart2 = " from CFDIAPP.COMPROBANTE comp inner join CFDIAPP.CLIENTE cli on comp.idcliente=cli.idcliente"
				+ " inner join CFDIAPP.DATOSFISCALES df on df.iddatosfiscales = cli.iddatosfiscales"
				+ " inner join CFDIAPP.METODOPAGO mp on mp.idmetodopago = comp.idmetodopago"
				+ " left join CFDIAPP.COMPROBANTECREDITO cc on cc.idcomprobantecredito=comp.idcomprobante";

		String queryPart3 = " where comp.estatus!=3 and comp.idnegocio = :idNegocio ";

		if (null != extras && extras.size() > 0) {

			int i = 1;
			for (String itemExtra : extras) {

				queryPart1 += ", ex" + i + ".valorEX" + i;

				queryPart2 += " left join (select ve.valor as valorEX"
						+ i
						+ ", ve.idcomprobante as idCompEX"
						+ i
						+ ", ce.idnegocio as idNegEx"
						+ i
						+ ", ce.nombrecampo as nameEX"
						+ i
						+ " from"
						+ " CFDIAPP.VALORESEXTRACOMPROBANTE ve"
						+ " inner join CFDIAPP.CAMPOEXTRACOMPROBANTE ce on ce.idcampoextracomprobante=" +
						"ve.idcampoextracomprobante) "
						+ " ex" + i + " on ex" + i + ".idCompEx" + i + "=comp.idComprobante and ex" + i + ".idNegEx" + i
						+ "=comp.idNegocio and ex" + i + ".nameEx" + i + "=\'" + itemExtra + "\'";

				i++;
			}

		}

		String queryStr = queryPart1 + queryPart2 + queryPart3;

		Set set = criterios.entrySet();
		Iterator i = set.iterator();
		while (i.hasNext()) {
			Map.Entry me = (Map.Entry) i.next();

			if (me.getKey().equals("folio")) {
				queryStr += "and comp.folio like :folio ";
			} else if (me.getKey().equals("serie")) {
				queryStr += "and comp.serie like :serie ";
			} else if (me.getKey().equals("rfc")) {
				queryStr += "and df.rfc like :rfc ";
			} else if (me.getKey().equals("fechaInicio")) {
				//queryStr += "and DATE(comp.fechatimbrado) between :startDate and :endDate ";
				queryStr += "and (DATE(comp.fechaCreacion) between :startDate and :endDate or " +
						"DATE(comp.fechatimbrado) between :startDate2 and :endDate2)";
			} else if (me.getKey().equals("metodoPago")) {
				queryStr += "and mp.descripcion=:metodoPago ";
			} else if (me.getKey().equals("onlyParcial")) {
				queryStr += "and (comp.montoparcialidades is not null and comp.parcialidades is not null)";
			} else if (me.getKey().equals("onlyCredit")) {
				queryStr += "and (cc.liquidado is not null or cc.fechaplaneadapago is not null)";
			}
		}

		queryStr = startQuery + queryStr + endQuery;

		String idNegocio = negocio!=null?negocio.getIdNegocio().toString():"0";
		Query query = getSession().createSQLQuery(queryStr).setString("idNegocio", idNegocio);

		i = set.iterator();
		while (i.hasNext()) {
			Map.Entry me = (Map.Entry) i.next();

			if (me.getKey().equals("folio")) {
				query.setString("folio", "%"+me.getValue().toString()+"%");
			} else if (me.getKey().equals("serie")) {
				query.setString("serie", "%"+me.getValue().toString().toUpperCase()+"%");
			} else if (me.getKey().equals("rfc")) {
				query.setString("rfc", "%"+me.getValue().toString().toUpperCase()+"%");
			} else if (me.getKey().equals("fechaInicio")) {
				Date dI = (Date) me.getValue();
				query.setDate("startDate", dI);
				query.setDate("startDate2", dI);
			} else if (me.getKey().equals("fechaFin")) {
				Date dF = (Date) me.getValue();
				query.setDate("endDate", dF);
				query.setDate("endDate2", dF);
			} else if (me.getKey().equals("metodoPago")) {
				query.setString("metodoPago", me.getValue().toString());
			}
		}

		List comprobantelist = query.list();

		return comprobantelist.size() > 0 ? comprobantelist : null;
	}

	@SuppressWarnings("rawtypes")
	@Override
	@Transactional public List getComprobanteByAllFieldsReport(Negocio negocio, 
			String toSearch, List<String> extras) throws DataAccessException {

		String queryPart1 = "select UCASE(comp.serie) as serie, cast(comp.folio as Integer) as folio, " +
				"comp.fechaCreacion as fecha, df.rfc as receptor, comp.uuid as uuid, comp.tipocomprobante as tipocfdi," // 6
				+ " case when comp.estatus=0 then varchar('Cancelado') when comp.estatus=1 then varchar('Vigente') when " +
				"comp.estatus=2 then varchar('Borrador') when comp.estatus=4 then varchar('Sin timbrar') end as estado, " +
				"comp.moneda as moneda," // 2
				+ " comp.tipoDeCambio as tipoDeCambio, comp.subTotal as subTotal, comp.total as total, comp.idComprobante, " +
				"mp.descripcion, comp.montoparcialidades, comp.parcialidades, comp.numeroparcialidad, "
				+ " cc.liquidado, cc.fechaplaneadapago, comp.descuentoGeneral, comp.fechaTimbrado";

		String queryPart2 = " from CFDIAPP.COMPROBANTE comp inner join CFDIAPP.CLIENTE cli on comp.idcliente=cli.idcliente"
				+ " inner join CFDIAPP.DATOSFISCALES df on df.iddatosfiscales = cli.iddatosfiscales"
				+ " inner join CFDIAPP.METODOPAGO mp on mp.idmetodopago = comp.idmetodopago"
				+ " left join CFDIAPP.COMPROBANTECREDITO cc on cc.idcomprobantecredito=comp.idcomprobante";

		String queryPart3 = " where comp.estatus!=3 and comp.idnegocio = :idNegocio ";

		if (null != extras && extras.size() > 0) {

			int i = 1;
			for (String itemExtra : extras) {

				queryPart1 += ", ex" + i + ".valorEX" + i;

				queryPart2 += " left join (select ve.valor as valorEX"
						+ i
						+ ", ve.idcomprobante as idCompEX"
						+ i
						+ ", ce.idnegocio as idNegEx"
						+ i
						+ ", ce.nombrecampo as nameEX"
						+ i
						+ " from"
						+ " CFDIAPP.VALORESEXTRACOMPROBANTE ve"
						+ " inner join CFDIAPP.CAMPOEXTRACOMPROBANTE ce on ce.idcampoextracomprobante=ve.idcampoextracomprobante) "
						+ " ex" + i + " on ex" + i + ".idCompEx" + i + "=comp.idComprobante and ex" + i + ".idNegEx" + i
						+ "=comp.idNegocio and ex" + i + ".nameEx" + i + "=\'" + itemExtra + "\'";

				i++;
			}
		}

		String queryStr = queryPart1 + queryPart2 + queryPart3;

		String toSearchLikeForm = "%" + toSearch + "%";

		queryStr += "and  (comp.serie like :serie ";
		queryStr += "or comp.folio like :folio ";
		queryStr += "or df.rfc like :rfc )";
		
		queryStr += " order by serie, folio, receptor";

		String idNegocio = negocio!=null?negocio.getIdNegocio().toString():"0";
		Query query = getSession().createSQLQuery(queryStr).setString("idNegocio", idNegocio);

		query.setString("folio", toSearchLikeForm);
		query.setString("serie", toSearchLikeForm.toUpperCase());
		query.setString("rfc", toSearchLikeForm.toUpperCase());

		List comprobantelist = query.list();

		return comprobantelist.size() > 0 ? comprobantelist : null;
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional public ArrayList<Object> getByUUIDLike(String uUID, Negocio negocio) throws DataAccessException {
		String toSearchLikeForm = uUID + "%";
		List<String> comprobantes = getHibernateTemplate().find(
				"select comprobante.uuid " + "from Comprobante as comprobante " + "where comprobante.uuid like ? "
						+ "and comprobante.negocio.idNegocio=? " + "and comprobante.parcialidades > 0 "
						+ "and comprobante.montoParcialidades >= 0 " + "and (comprobante.version = 2.2 "
						+ "or comprobante.version = 3.2)", toSearchLikeForm, negocio.getIdNegocio());

		ArrayList<Object> listaUuids = new ArrayList<Object>();

		if (comprobantes.size() > 0) {
			for (int i = 0; i < comprobantes.size(); i++)
				listaUuids.add(comprobantes.get(i));
		} else {
			listaUuids = null;
		}
		return listaUuids;
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional public List<Comprobante> getComprobantesBySerie(final Negocio negocio, 
			final String serie) throws DataAccessException {
		List<Comprobante> toReturn = getHibernateTemplate().find(
				" from Comprobante as c" + " where c.negocio=? and" + " c.serie=? and" + " c.estatus=?", negocio, serie,
				Comprobante.ESTADO_FACTURA_ACTIVA);
		return toReturn;
	}

	@Override
	@Transactional public void save(List<Comprobante> comprobantes) {
		getHibernateTemplate().saveOrUpdateAll(comprobantes);
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional public Comprobante getComprobanteBySerieyFolio(Negocio negocio, 
			String serie, String folio) throws DataAccessException {

		List<Comprobante> toReturn = getHibernateTemplate().find(
				" from Comprobante as c" + " where c.negocio=? and" + " c.serie=? and" + " c.folio=?", negocio, serie, folio);

		return toReturn.size() > 0 ? toReturn.get(0) : null;
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional public List<Comprobante> getReport2010(Negocio negocio, 
			Calendar fechaInicio, Calendar fechaFin) throws DataAccessException {
		List<Comprobante> comprobantes = getHibernateTemplate()
				.find(" from Comprobante as c inner join fetch c.cliente where c.negocio=? and c.estatus in (0, 1) and "+
						"((c.fechaCreacion>=? and c.fechaCreacion<=?) or (c.fechaCancelacion>=? and c.fechaCancelacion<=?)) " +
						"order by c.serie, c.folio asc",
						negocio, fechaInicio, fechaFin, fechaInicio, fechaFin);
						//"((c.fechaTimbrado>=? and c.fechaTimbrado<=?) or (c.fechaCancelacion>=? and c.fechaCancelacion<=?))",
		return comprobantes;
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional public Comprobante getBySerieFolio(String serie, String folio,
			Negocio negocio) throws DataAccessException {
		List<Comprobante> comprobantes = getHibernateTemplate()
		.find(" from Comprobante as comprobante " +
				"where comprobante.serie=? " +
				"and   comprobante.folio=? " +
				"and   comprobante.negocio.idNegocio=?",
				serie, folio, negocio.getIdNegocio());
		return comprobantes.size() > 0 ? comprobantes.get(0) : null;
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional public List<Object[]> findComprobanteByIdExterno(final Negocio negocio,
			final String idExterno) throws DataAccessException {
		getHibernateTemplate().setMaxResults(1);
		List<Object[]> toReturn = getHibernateTemplate().find(
				" select c.idExterno, c.fechaCreacion" +
				" from Comprobante as c" +
				" where c.negocio=?" +
				" and c.idExterno=?",negocio,idExterno);
		getHibernateTemplate().setMaxResults(0);
		return toReturn;
	}
	
}
